import requests
import pymysql

api_url = "https://api.github.com/search/repositories?q=spider"

req = requests.get(api_url)

print('状态码：', req.status_code)

req_dic = req.json()

print('与spider有关的库总数：', req_dic["total_count"])

print('本次请求是否完整：', req_dic["incomplete_results"])

req_dic_items = req_dic["items"]

print('当前页面返回的项目数量：', len(req_dic_items))

db = pymysql.connect(host='localhost', user='root', password="926979", port=3306)
cursor = db.cursor()
cursor.execute("CREATE DATABASE IF NOT EXISTS WEBAPI3 DEFAULT CHARACTER SET utf8mb4")
db.close()

db2 = pymysql.connect(host="localhost", user="root", password="926979", database="WEBAPI3", port=3306)
cursor2 = db2.cursor()
cursor2.execute("DROP TABLE IF EXISTS webapi3")
sql1 = """CREATE TABLE webapi3 (
                id int(10) NOT NULL AUTO_INCREMENT,
                full_name char(100) NOT NULL,
                score int(10) NOT NULL,
                PRIMARY KEY (id)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"""
cursor2.execute(sql1)
print("Created table Successfully.")

for index, key in enumerate(req_dic_items):
    print("项目序号", index, "项目名称：", key["full_name"], '项目评分：', key['score'])
    sql2 = 'INSERT INTO webapi3(id, full_name, score) VALUES (%s, %s, %s)'
    try:
        cursor2.execute(sql2, (index, key['full_name'], key['score']))
        db2.commit()
    except:
        db2.rollback()

db2.close()
#在创建数据库时，加入了 IF NOT EXISTS 来避免重复创建。
#在连接数据库时，修改了参数的传递方式。
#在创建表的 SQL 语句中，去掉了多余的引号。
#在循环插入数据时，将插入语句放在循环内部，确保每次都会插入新的数据